from sqlalchemy import create_engine, func
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import sessionmaker

from commons.entity.po.approval import Approval
from commons.entity.po.travel import Travel
from commons.mappers.base_mapper import BaseMapper

# 创建数据库引擎
db_url = "mysql+pymysql://root:123456@localhost/ctdb"
engine = create_engine(db_url, echo=True)

# 创建会话类
Session = sessionmaker(bind=engine)


class ApprovalMapper(BaseMapper):
    def __init__(self):
        super().__init__(Session)

    # def insert(self, approval_data):
    #     """
    #     插入记录
    #     :param approval_data:
    #     :return: 成功返回 True， 否则 False
    #     """
    #     try:
    #         with self.session_factory() as session:
    #             # 获取最大 travel_id
    #             max_travel_id = session.query(func.max(Travel.travel_id)).scalar()
    #
    #             approval = Approval(
    #                 travel_approval=approval_data.travel_approval,
    #                 advance_approval=approval_data.advance_approval,
    #                 reimbursement_approval_ma=approval_data.reimbursement_approval_ma,
    #                 reimbursement_approval_fi=approval_data.reimbursement_approval_fi,
    #                 travel_rejection=approval_data.travel_rejection,
    #                 advance_rejection=approval_data.advance_rejection,
    #                 reimbursement_rejection_ma=approval_data.reimbursement_rejection_ma,
    #                 reimbursement_rejection_fi=approval_data.reimbursement_rejection_fi,
    #                 travel_id=max_travel_id,
    #                 user_id=approval_data.user_id
    #             )
    #             session.add(approval)
    #             session.commit()
    #             return True
    #     except SQLAlchemyError as e:
    #         session.rollback()
    #         print(f"Database error during insert operation: {str(e)}")
    #         return False

    def select_by_approval_id(self, approval_id):
        """
        根据 approval_id 从数据库获取 Approval 对象
        :param approval_id: str
        :return: Approval 对象或 None
        """
        try:
            with self.session_factory() as session:
                approval = session.query(Approval).filter_by(approval_id=approval_id).first()
                if approval:
                    return approval
                return None
        except SQLAlchemyError as e:
            print(f"Database error: {str(e)}")
            return None

    def delete_by_approval_id(self, approval_id):
        """
        根据 approval_id 删除审批记录
        :param approval_id: str
        :return: True（删除成功）或 False（失败）
        """
        try:
            with self.session_factory() as session:
                approval = session.query(Approval).filter_by(approval_id=approval_id).first()
                if approval:
                    session.delete(approval)
                    session.commit()  # 提交事务
                    return True
                return False  # 审批记录不存在
        except SQLAlchemyError as e:
            print(f"Database error during delete operation: {str(e)}")
            return False

    def update_by_approval_id(self, approval_update, approval_id):
        """
        根据 approval_id 更新审批记录信息
        :param approval_id: str
        :param approval_update: Approval 类实例或字典，包含需要更新的审批记录属性
        :return: True（更新成功）或 False（失败）
        """
        try:
            with self.session_factory() as session:
                approval = session.query(Approval).filter_by(approval_id=approval_id).first()
                if approval:
                    # 如果 approval_update 是类实例，转换为字典
                    if approval_update and hasattr(approval_update, 'to_dict'):
                        approval_update = approval_update.to_dict()

                    # 使用新数据更新审批记录属性
                    for key, value in approval_update.items():
                        if value is not None and hasattr(approval, key):
                            setattr(approval, key, value)

                    session.commit()  # 提交事务
                    return True
                return False  # 审批记录不存在
        except SQLAlchemyError as e:
            print(f"Database error during update operation: {str(e)}")
            return False

    def select_by_travel_id(self, model, travel_id):
        """
        根据 travel_id 获取审批记录
        :param model: 要查询的模型类（如 Approval）
        :param travel_id: 出差 ID，用于查询对应的审批记录
        :return: 查询结果，通常是一个记录字典或对象
        """
        # 使用 SQLAlchemy 查询，根据 travel_id 获取审批记录
        approval = self.session.query(model).filter(model.travel_id == travel_id).first()
        return approval  # 返回第一个符合条件的记录（或者返回 None，如果没有记录）

    def select_approve_by_travel_id(self, travel_id):
        """
        根据 travel_id 从数据库获取 Approval 对象
        :param travel_id:
        :return: Approval 对象或 None
        """
        try:
            with self.session_factory() as session:
                approve = session.query(Approval).filter_by(travel_id=travel_id).first()
                if approve:
                    return approve
                return None
        except SQLAlchemyError as e:
            print(f"Database error: {str(e)}")
            return None

